package com.ebank.controller;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

public class TransactionCL extends HttpServlet {
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		HttpSession session=request.getSession();
		PrintWriter out = response.getWriter();
		if(session.getAttribute("username")!=null){
		
		
		Date date=new Date();
		SimpleDateFormat df=new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
		String time=df.format(date);
		
		
		Statement stat = null;
		Connection con=null;
		ResultSet rs=null;
		ResultSet rs0=null;
		ResultSet rs2=null;
		ResultSet rs3=null;
		PreparedStatement pst = null;
		PreparedStatement pst0 = null;
		PreparedStatement pst1 = null;
		PreparedStatement pst2 = null;
		PreparedStatement pst3 = null;
		PreparedStatement pst4 = null;
		PreparedStatement pst5 = null;
		String selectSQL = "select * from transaction";
		String loginName=(String) (session.getAttribute("username"));
		String TransactionUsername =request.getParameter("TransactionUsername");
		double balance=0.0;
		double balance2=0.0;
		double amount =Double.parseDouble(request.getParameter("amount"));
try {
	Class.forName("com.mysql.jdbc.Driver");
	con = DriverManager.getConnection(
	      "jdbc:mysql://localhost/user?useUnicode=true&characterEncoding=Big5",
	      "root","root");
	//loading user balance form
	pst2=con.prepareStatement("select * from account WHERE username=?");
	pst2.setString(1, loginName);
	rs2=pst2.executeQuery();
	while(rs2.next()){
		 rs2.getDouble(3);
balance=rs2.getDouble(3);
 }
	//loading user balance to
	pst3=con.prepareStatement("select * from account WHERE username=?");
	pst3.setString(1, TransactionUsername);
	rs3=pst3.executeQuery();
	while(rs3.next()){
		 rs3.getDouble(3);
balance2=rs3.getDouble(3);
 }
		//if user
		pst0=con.prepareStatement("select * from account where username=?");
		pst0.setObject(1, TransactionUsername);
		rs0=pst0.executeQuery();
		if(rs0.next()){
			if(balance>amount){
	//- user amount
	 pst=con.prepareStatement("UPDATE account SET balance=? where username=?");
	 pst.setObject(1, balance-amount);
	 pst.setObject(2, loginName);
	 pst.execute();
	 //+ user amount
	 pst1=con.prepareStatement("UPDATE account SET balance=? where username=?");
	 pst1.setObject(1, balance2+amount);
	 pst1.setObject(2, TransactionUsername);
	 pst1.execute();
	 //- user log
	 pst4=con.prepareStatement(" INSERT INTO transaction VALUES (?, ?,?,?,?,?)");
	 pst4.setObject(1, time);
	 pst4.setObject(2, loginName);
	 pst4.setObject(3, 0);
	 pst4.setObject(4, amount);
	 pst4.setObject(5, TransactionUsername);
	 pst4.setObject(6, balance-amount);
	 pst4.execute();
	 //+user log
	 pst5=con.prepareStatement(" INSERT INTO transaction VALUES (?, ?,?,?,?,?)");
	 pst5.setObject(1, time);
	 pst5.setObject(2, TransactionUsername);
	 pst5.setObject(3, amount);
	 pst5.setObject(4, 0);
	 pst5.setObject(5, loginName);
	 pst5.setObject(6, balance+amount);
	 pst5.execute();
	 
	 
	 out.print("Success.......Please Wait"); 
	 response.setHeader("refresh", "2;URL=Main");
	 }else{
		 out.print("Not enough amount"); 
		 response.setHeader("refresh", "2;URL=Main");
	 }
	}
	else{
		out.print("No User Account......"); 
		 response.setHeader("refresh", "2;URL=Transaction");}
} catch (Exception e) {
	}finally{
		if(rs!=null){
			try {
				rs.close();
				rs0.close();
				rs2.close();
				rs3.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
			rs=null;
		}
		if(pst!=null){
			try {
				pst.close();
				pst0.close();
				pst1.close();
				pst2.close();
				pst3.close();
				pst4.close();
				pst5.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
			pst=null;
		}
		if(con!=null){
			try {
				con.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
			con=null;
		}
	}}else{ out.print("Wong Please Login <a href=/EBanking/Login>Login</a>");
	} 
	}
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
this.doGet(request, response);
	}

}
